/*
 * Licensed to Nanjing RedOrange Co. ltd (RedOrange) under one or more
 * contributor license agreements. See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * RedOrange licenses this file to You under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License. You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations
 * under the License.
 */
package redora.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.mysql.jdbc.JDBC4Connection;
import com.mysql.jdbc.JDBC4PreparedStatement;

import redora.api.fetch.Page;
import redora.exceptions.ConnectException;
import redora.exceptions.PagingException;
import redora.exceptions.QueryException;

/**
 * Base class for the java service entry classes providing the database
 * connection management. This class will cache all prepared statements and it
 * will maintain a database connection. With the makePreparedStatement function
 * a hash in the SQL query determines if a prepared statement is already in the
 * cache.
 *
 * @author jaap
 */
public abstract class ServiceBase {

    private Logger l = Logger.getLogger("redora.impl.ServiceBase");
    protected JDBC4Connection con;
    protected JDBC4PreparedStatement ps;
    protected Statement s;
    protected Map<String, JDBC4PreparedStatement> psMap;
    protected ResultSet rs;

    /**
     * Initializer and connects with the database.
     *
     * @throws ConnectException
     *             When a connection cannot be established.
     */
    public ServiceBase() throws ConnectException {
        connect();
    }

    /**
     * Connects to the database when the is no connect, or the connection was
     * closed.
     */
    public void connect() throws ConnectException {
        if (con == null) {
            l.log(Level.FINE, "Connecting to DB");
            con = DatabaseFactory.connect();
        } else if (con.isClosed()) {
            close();
            l.log(Level.FINE, "Reconnecting to DB");
            con = DatabaseFactory.connect();
        } else {
            l.log(Level.FINE, "reusing connection");
        }
    }

    /**
     * Closes the database connection and all statements and resultset.
     * Exceptions are silently discarded.
     */
    public void close() {
        l.log(Level.FINE, "closing");
        try {
            if (rs != null && !rs.isClosed()) {
                rs.close();
            }
        } catch (SQLException e) {
            l.log(Level.WARNING, "Failed to close resultset", e);
        }
        rs = null;
        try {
            if (s != null && !s.isClosed()) {
                s.close();
            }
        } catch (SQLException e) {
            l.log(Level.WARNING, "Failed to close statement", e);
        }
        s = null;
        if (psMap != null) {
            for (JDBC4PreparedStatement psClose : psMap.values()) {
                try {
                    psClose.close();
                } catch (SQLException e) {
                    l.log(Level.WARNING, "Failed to close "
                         + "prepared statement", e);
                }
            }
        }
        psMap = null;
        if (con != null && !con.isClosed()) {
            try {
                con.close();
            } catch (SQLException e) {
                l.log(Level.WARNING, "Failed to close connection", e);
            }
        }
        con = null;
    }

    /**
     * Creates a Prepared Statement on private variable ps. If this statement
     * was already created before it will retrieve the statement from the cache,
     * otherwise a new PreparedStatement is created.
     *
     * @param sql
     *            SQL statement just like you used in creating a
     *            PreparedStatement
     */
    protected void makePreparedStatement(String sql)
           throws SQLException, ConnectException {
        l.log(Level.FINE, "Preparing statement");
        connect();
        if (psMap == null) {
            psMap = new HashMap<String, JDBC4PreparedStatement>();
        }
        ps = psMap.get(sql);
        if (ps == null || ps.isClosed()) {
            l.log(Level.FINE, "Creating new statement");
            JDBC4PreparedStatement tempPs = (JDBC4PreparedStatement)
              con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            tempPs.setFetchDirection(ResultSet.FETCH_FORWARD);
            psMap.put(sql, tempPs);
            ps = tempPs;
        }
    }

    /**
     * Looks up the database meta data to determine if the table exists.
     *
     * @return True if the table exists.
     **/
    public boolean tableExists(String tableName)
           throws ConnectException, QueryException {
        connect();
        boolean retVal;

        try {
            rs = con.getMetaData().getTables(null, null, tableName, null);
            retVal = rs.next();
        } catch (SQLException e) {
            l.log(Level.SEVERE, "Failed to open meta data to "
                    + "find table " + tableName, e);
            throw new QueryException("Failed to open meta data to "
                    + "find table " + tableName, e);
        }

        return retVal;
    }

    /**
     * Retrieves the last attribute name on which the unique key is defined.
     *
     * @param tableName
     *            (Mandatory) table name
     * @param keyName
     *            (Mandatory) unique index name
     * @return null, or the attribute name of the index
     */
    public String uniqueKeyAttribute(String tableName, String keyName)
            throws ConnectException, QueryException {
        String retval = null;

        connect();
        ResultSet r = null;
        try {
            r = con.getMetaData().getIndexInfo(null, null,
                    tableName, true, true);
            while (r.next()) {
                if (keyName.equalsIgnoreCase(r.getString("INDEX_NAME"))) {
                    retval = r.getString("COLUMN_NAME");
                    // TODO now it gets the last value: maybe it should get the
                    // first.
                }
            }
        } catch (SQLException e) {
            throw new QueryException("Cannot retrieve Index: " + keyName, e);
        } finally {
            try {
                if (r != null) {
                    r.close();
                }
            } catch (SQLException e) {
                l.log(Level.INFO, "Ignoring closing error", e);
            }
        }

        return retval;
    }

    /**
     * Get the total size of the resultSet for the given SQL.
     *
     * @param SQL
     * The SQL sentence to execute.
     */
    public int getResultSize(String SQL) throws ConnectException,
            PagingException, QueryException {
        int resultSize = 0;
        String getSizeSQL = SQL.replaceAll(SQL.substring(SQL.indexOf("select")
                + 7, SQL.indexOf("from") - 1), "count(1)");
        try {
            makePreparedStatement(getSizeSQL);
            rs = ps.executeQuery();
            if (rs.next()) {
                resultSize = rs.getInt(1);
            }
            rs.close();
            rs = null;
        } catch (SQLException e) {
            l.log(Level.SEVERE, "Failed to run query " + getSizeSQL, e);
            throw new QueryException("Failed to run query: " + getSizeSQL, e);
        }
        return resultSize;
    }

    /**
     * Add page info to the given SQL.
     *
     * @throws PagingException
     */
    public String addPagingForSQL(String SQL, Page page)
          throws PagingException {
        return SQL + " limit " + page.position() * page.pageSize()
               + "," + page.pageSize();
    }

    @Override
    protected void finalize() throws Throwable {
        l.log(Level.FINE, "finalizing");
        close();
        super.finalize();
    }
}
